﻿namespace dogd
{
	using System;
	using System.Collections.Generic;
	using System.Data.Common;
	using System.Reflection;

	/// <summary>
	/// Implementation of IRepository targetted at a Sqlite database
	/// </summary>
	[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Naming", "CA1704:IdentifiersShouldBeSpelledCorrectly", MessageId = "Sqlite", Justification = "As intended")]
	public class Repository : IRepository 
	{
		private static SqlStatementCache cache = new SqlStatementCache();
		private static readonly object lockingObject = new object();

		/// <summary>
		/// Initialize default instance
		/// </summary>
		public Repository(IConnectionProvider connectionProvider, IMetadataProvider metadataProvider, IParameterFactory parameterFactory)
		{
			if (connectionProvider == null)
			{
				throw new ArgumentNullException("connectionProvider");
			}

			if (metadataProvider == null)
			{
				throw new ArgumentNullException("metadataProvider");
			}

			if (parameterFactory == null)
			{
				throw new ArgumentNullException("parameterFactory");
			}

			this.ConnectionProvider = connectionProvider;
			this.MetadataProvider = metadataProvider;
			this.ParameterFactory = parameterFactory;
		}

		/// <summary>
		/// The instance of the connection provider.
		/// </summary>
		protected IConnectionProvider ConnectionProvider
		{
			get;
			private set;
		}

		/// <summary>
		/// The instance of the metadata provider.
		/// </summary>
		protected IMetadataProvider MetadataProvider
		{
			get;
			private set;
		}

		/// <summary>
		/// The instance of the parameter factory.
		/// </summary>
		protected IParameterFactory ParameterFactory
		{
			get;
			private set;
		}

		/// <summary>
		/// Creates a new repository using the provided sql script text.
		/// </summary>
		/// <param name="sqlText"></param>
		public void CreateNewRepository(string sqlText)
		{
			this.Execute(sqlText, RunScript);
		}

		/// <summary>
		/// Saves the provided instance to the repository.
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="newInstance"></param>
		/// <returns></returns>
		public void Add<T>(T newInstance) where T : new()
		{
			if (newInstance == null)
			{
				throw new ArgumentNullException("newInstance");
			}

			this.Execute<T>(newInstance, this.Insert<T>);
		}

		/// <summary>
		/// Saves the provided instance using an existing transaction.
		/// </summary>
		/// <typeparam name="T">The type of the instance.</typeparam>
		/// <param name="newInstance">The object instance.</param>
		/// <param name="connection">The database connection.</param>
		/// <param name="transaction">The current transaction.</param>
		protected void Insert<T>(T newInstance, DbConnection connection, DbTransaction transaction) where T : new()
		{
			if (newInstance == null)
			{
				throw new ArgumentNullException("newInstance");
			}

			if (connection == null)
			{
				throw new ArgumentNullException("connection");
			}

			if (transaction == null)
			{
				throw new ArgumentNullException("transaction");
			}

			var instanceType = typeof(T);
			var metadata = this.MetadataProvider.LoadDataFor(instanceType);

			using (DbCommand command = connection.CreateCommand(this.GetSql(CrudOption.Insert, metadata), transaction))
			{
				var builder = new ParameterBuilder<T>(this.ParameterFactory);

				builder.ConstructForWrite(
					newInstance,
					metadata,
					ParameterOption.ExcludePrimaryKey,
					command);

				string text = command.ExecuteScalar().ToString();
				ColumnMetadata column = metadata.PrimaryKey;

				if (column.IsPrimaryKey)
				{
					PropertyInfo pi = instanceType.GetProperty(column.PropertyName);
					int id = Convert.ToInt32(text);
					pi.SetValue(newInstance, id, BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Instance, null, null, null);
				}
			}
		}

		/// <summary>
		/// Read instance of T given a lookup value.
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="guid"></param>
		/// <returns></returns>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1004:GenericMethodsShouldProvideTypeParameter")]
		public T Read<T>(string guid) where T : new()
		{
			if (string.IsNullOrEmpty(guid))
			{
				throw new ArgumentNullException("guid");
			}

			try
			{
				return this.Read<T>(guid, null);
			}
			catch (DbException ex)
			{
				throw new PersistenceException("Error retrieving object by id", ex);
			}
		}

		/// <summary>
		/// Read an instance of T given the primary key.
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="primaryKey"></param>
		/// <returns></returns>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1004:GenericMethodsShouldProvideTypeParameter")]
		public T Read<T>(Key primaryKey) where T : new()
		{
			if (primaryKey == null || !primaryKey.IsValid)
			{
				throw new ArgumentNullException("primaryKey");
			}

			try
			{
				return this.Read<T>(primaryKey, null);
			}
			catch (DbException ex)
			{
				throw new PersistenceException("Error retrieving object by primary key", ex);
			}
		}

		/// <summary>
		/// Read an instance of T given a primary key and a transaction
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="primaryKey"></param>
		/// <param name="transaction"></param>
		/// <returns></returns>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1004:GenericMethodsShouldProvideTypeParameter", Justification = "As intended")]
		protected T Read<T>(Key primaryKey, DbTransaction transaction) where T : new()
		{
			if (primaryKey == null || !primaryKey.IsValid)
			{
				throw new ArgumentNullException("primaryKey");
			}

			if (transaction == null)
			{
				throw new ArgumentNullException("transaction");
			}

			var metadata = this.MetadataProvider.LoadDataFor(typeof(T));

			using (DbConnection connection = this.ConnectionProvider.Connection)
			{
				using (DbCommand query = connection.CreateCommand(this.GetSql(CrudOption.SelectByPrimaryKey, metadata), transaction))
				{
					var builder = new ParameterBuilder<T>(this.ParameterFactory);

					builder.ConstructForRead(
						primaryKey,
						metadata.PrimaryKey,
						query);

					return ReadObject<T>(query, metadata);
				}
			}
		}
		
		/// <summary>
		/// Read a complete list of all instances of T in a table
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <returns></returns>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1004:GenericMethodsShouldProvideTypeParameter")]
		public ICollection<T> ReadAll<T>() where T : new()
		{
			try
			{
				var metadata = this.MetadataProvider.LoadDataFor(typeof(T));
				string sql = this.GetSql(CrudOption.Select, metadata);

				return ReadList<T>(sql, metadata);
			}
			catch (DbException ex)
			{
				throw new PersistenceException("Error retrieving all objects", ex);
			}
		}

		/// <summary>
		/// Read a list of all instances of T in a table - filtered version
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <returns></returns>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1004:GenericMethodsShouldProvideTypeParameter")]
		public ICollection<T> ReadAll<T>(FilterByCollection filters) where T : new()
		{
			try
			{
				var metadata = this.MetadataProvider.LoadDataFor(typeof(T));

				string sql = this.GetSql(CrudOption.Select, metadata, filters);

				return ReadList<T>(sql, metadata);
			}
			catch (DbException ex)
			{
				throw new PersistenceException("Error retrieving all objects", ex);
			}
		}

		/// <summary>
		/// Read all instances of T corresponding to the foreign key
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="foreignKey"></param>
		/// <returns></returns>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1004:GenericMethodsShouldProvideTypeParameter")]
		public ICollection<T> ReadAll<T>(Key foreignKey) where T : new()
		{
			if (foreignKey == null || !foreignKey.IsValid)
			{
				throw new ArgumentNullException("foreignKey");
			}

			try
			{
				using (DbConnection connection = this.ConnectionProvider.Connection)
				{
					return this.ReadAll<T>(foreignKey, connection, null);
				}
			}
			catch (DbException ex)
			{
				throw new PersistenceException("Error retrieving objects by foreignKey", ex);
			}
		}

		/// <summary>
		/// Read instances of T corresponding to a lookup value.
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="lookupId"></param>
		/// <param name="transaction"></param>
		/// <returns></returns>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1004:GenericMethodsShouldProvideTypeParameter", Justification = "As intended")]
		protected T Read<T>(string lookupId, DbTransaction transaction) where T : new()
		{
			if (string.IsNullOrEmpty(lookupId))
			{
				throw new ArgumentNullException("lookupId");
			}

			if (transaction == null)
			{
				throw new ArgumentNullException("transaction");
			}

			var metadata = this.MetadataProvider.LoadDataFor(typeof(T));

			using (DbConnection connection = this.ConnectionProvider.Connection)
			{
				using (DbCommand query = connection.CreateCommand(this.GetSql(CrudOption.SelectById, metadata), transaction))
				{
					var builder = new ParameterBuilder<T>(this.ParameterFactory);

					builder.ConstructForRead(
						EscapeGuid(lookupId),
						metadata.Lookup,
						query);

					return ReadObject<T>(query, metadata);
				}
			}
		}

		/// <summary>
		/// Read all instances of T corresponding to the foreign key value.
		/// </summary>
		/// <typeparam name="T">The type of the object.</typeparam>
		/// <param name="foreignKey">The foreign key to query against.</param>
		/// <param name="connection">The database connection.</param>
		/// <param name="transaction">The current transaction.</param>
		/// <returns></returns>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1004:GenericMethodsShouldProvideTypeParameter", Justification = "As intended")]
		protected ICollection<T> ReadAll<T>(Key foreignKey, DbConnection connection, DbTransaction transaction) where T : new()
		{
			if (foreignKey == null || !foreignKey.IsValid)
			{
				throw new ArgumentNullException("foreignKey");
			}

			if (connection == null)
			{
				throw new ArgumentNullException("connection");
			}

			if (transaction == null)
			{
				throw new ArgumentNullException("transaction");
			}

			var metadata = this.MetadataProvider.LoadDataFor(typeof(T));

			using (DbCommand query = connection.CreateCommand(this.GetSql(CrudOption.SelectByForeignKey, metadata), transaction))
			{
				var builder = new ParameterBuilder<T>(this.ParameterFactory);
				builder.ConstructForRead(
					foreignKey,
					metadata.ForeignKey,
					query);

				return ReadList<T>(query, metadata);
			}
		}

		/// <summary>
		/// Search for instances of T containing the search text
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="searchTerm"></param>
		/// <returns></returns>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1004:GenericMethodsShouldProvideTypeParameter")]
		public ICollection<T> Search<T>(string searchTerm) where T : new()
		{
			throw new NotImplementedException();
		}

		/// <summary>
		/// Update the repository with values taken from the instance of T
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="existingInstance"></param>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1004:GenericMethodsShouldProvideTypeParameter", Justification = "As intended")]
		public void Update<T>(T existingInstance) where T : new()
		{
			if (existingInstance == null)
			{
				throw new ArgumentNullException("existingInstance");
			}

			this.Execute<T>(existingInstance, this.Update<T>);
		}

		/// <summary>
		/// Update the repository with values taken from the instance of T.
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="existingInstance"></param>
		/// <param name="connection"></param>
		/// <param name="transaction"></param>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1004:GenericMethodsShouldProvideTypeParameter", Justification = "As intended")]
		protected void Update<T>(T existingInstance, DbConnection connection, DbTransaction transaction) where T : new()
		{
			if (existingInstance == null)
			{
				throw new ArgumentNullException("existingInstance");
			}

			if (connection == null)
			{
				throw new ArgumentNullException("connection");
			}

			if (transaction == null)
			{
				throw new ArgumentNullException("transaction");
			}

			var metadata = this.MetadataProvider.LoadDataFor(typeof(T));

			using (DbCommand command = connection.CreateCommand(this.GetSql(CrudOption.Update, metadata), transaction))
			{
				var builder = new ParameterBuilder<T>(this.ParameterFactory);

				builder.ConstructForWrite(
					existingInstance,
					metadata,
					ParameterOption.IncludePrimaryKey,
					command);

				command.ExecuteNonQuery();
			}
		}

		/// <summary>
		/// Remove the instance of T corresponding to the primary key from the repository 
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="primaryKey"></param>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1004:GenericMethodsShouldProvideTypeParameter")]
		public void Remove<T>(Key primaryKey) where T : new()
		{
			if (primaryKey == null || !primaryKey.IsValid)
			{
				throw new ArgumentNullException("primaryKey");
			}

			this.Execute<T>(primaryKey, this.Remove<T>);
		}

		/// <summary>
		/// Remove all instances of T corresponding to the foreign key from the repository
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="foreignKey"></param>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1004:GenericMethodsShouldProvideTypeParameter")]
		public void RemoveAll<T>(Key foreignKey) where T : new()
		{
			if (foreignKey == null || !foreignKey.IsValid)
			{
				throw new ArgumentNullException("foreignKey");
			}

			this.Execute<T>(foreignKey, this.RemoveAll<T>);
		}

		/// <summary>
		/// Execute against an instance of a type.
		/// </summary>
		/// <typeparam name="T">The type of the object.</typeparam>
		/// <param name="instance">The specific instance.</param>
		/// <param name="action">The action delegate.</param>
		protected void Execute<T>(T instance, Action<T, DbConnection, DbTransaction> action)
		{
			try
			{
				lock (lockingObject)
				{
					using (DbConnection connection = this.ConnectionProvider.Connection)
					{
						using (var transaction = connection.BeginTransaction())
						{
							action(instance, connection, transaction);

							transaction.Commit();
						}
					}
				}
			}
			catch (DbException ex)
			{
				throw new PersistenceException("Error executing against object instance", ex);
			}
		}

		/// <summary>
		/// Execute an action given a dbkey.
		/// </summary>
		/// <typeparam name="T">The type to execute against.</typeparam>
		/// <param name="dbKey">The primary key.</param>
		/// <param name="action">The action delegate.</param>
		protected void Execute<T>(Key dbKey, Action<Key, DbConnection, DbTransaction> action)
		{
			try
			{
				lock (lockingObject)
				{
					using (DbConnection connection = this.ConnectionProvider.Connection)
					{
						using (var transaction = connection.BeginTransaction())
						{
							action(dbKey, connection, transaction);

							transaction.Commit();
						}
					}
				}
			}
			catch (DbException ex)
			{
				throw new PersistenceException("Error executing with db key", ex);
			}
		}

		/// <summary>
		/// Execute an action with some raw sql given a connection and a transaction.
		/// </summary>
		/// <param name="rawSql">The sql text to run.</param>
		/// <param name="action">The action delegate.</param>
		protected void Execute(string rawSql, Action<string, DbConnection, DbTransaction> action)
		{
			try
			{
				lock (lockingObject)
				{
					using (DbConnection connection = this.ConnectionProvider.Connection)
					{
						using (var transaction = connection.BeginTransaction())
						{
							action(rawSql, connection, transaction);

							transaction.Commit();
						}
					}
				}
			}
			catch (DbException ex)
			{
				throw new PersistenceException("Error executing sql", ex);
			}
		}

		/// <summary>
		/// Remove the instance of T corresponding to the primary key.
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="primaryKey"></param>
		/// <param name="connection"></param>
		/// <param name="transaction"></param>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1004:GenericMethodsShouldProvideTypeParameter", Justification = "As intended")]
		protected void Remove<T>(Key primaryKey, DbConnection connection, DbTransaction transaction) where T : new()
		{
			if (primaryKey == null || !primaryKey.IsValid)
			{
				throw new ArgumentNullException("primaryKey");
			}

			if (connection == null)
			{
				throw new ArgumentNullException("connection");
			}

			if (transaction == null)
			{
				throw new ArgumentNullException("transaction");
			}

			var metadata = this.MetadataProvider.LoadDataFor(typeof(T));

			using (var command = connection.CreateCommand(this.GetSql(CrudOption.Delete, metadata), transaction))
			{
				var builder = new ParameterBuilder<T>(this.ParameterFactory);

				builder.ConstructForRead(
					primaryKey,
					metadata.PrimaryKey,
					command);

				command.ExecuteNonQuery();
			}
		}

		/// <summary>
		/// Remove all records in T that match foreign key value
		/// </summary>
		/// <typeparam name="T"></typeparam>
		/// <param name="foreignKey"></param>
		/// <param name="connection"></param>
		/// <param name="transaction"></param>
		[System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1004:GenericMethodsShouldProvideTypeParameter")]
		protected void RemoveAll<T>(Key foreignKey, DbConnection connection, DbTransaction transaction) where T : new()
		{
			if (foreignKey == null || !foreignKey.IsValid)
			{
				throw new ArgumentNullException("foreignKey");
			}

			if (connection == null)
			{
				throw new ArgumentNullException("connection");
			}

			if (transaction == null)
			{
				throw new ArgumentNullException("transaction");
			}

			var metadata = this.MetadataProvider.LoadDataFor(typeof(T));

			using (DbCommand command = connection.CreateCommand(this.GetSql(CrudOption.DeleteByForeignKey, metadata), transaction))
			{
				var builder = new ParameterBuilder<T>(this.ParameterFactory);

				builder.ConstructForRead(
					foreignKey,
					metadata.ForeignKey,
					command);

				command.ExecuteNonQuery();
			}
		}

		/// <summary>
		/// Escape string (guid) values to allow correct storage and retrieval in sqlite
		/// </summary>
		/// <param name="guid"></param>
		/// <returns></returns>
		protected static string EscapeGuid(string guid)
		{
			if (guid.StartsWith("{", StringComparison.OrdinalIgnoreCase)
				|| guid.EndsWith("}", StringComparison.OrdinalIgnoreCase))
			{
				throw new ArgumentException("Guid is already escaped", "guid");
			}

			return "{" + guid + "}";
		}

		/// <summary>
		/// Gets the sql appropriate to the CrudOption and the TableMetadata provided
		/// </summary>
		/// <param name="option">Which action to perform</param>
		/// <param name="metadata">Metadata for the table to generate for</param>
		/// <returns></returns>
		protected string GetSql(CrudOption option, TableMetadata metadata)
		{
			//Trace.WriteLine("Finding sql for metadata " + metadata.MetadataType.ToString() + " and option " + option.ToDescription());
			string sql = cache.Find(metadata.MetadataType, option);

			if (string.IsNullOrEmpty(sql))
			{
				//Trace.WriteLine("Sql not found performing generation");
				sql = GenerateSql(option, metadata, null);
				//Trace.WriteLine("Generation complete : " + sql);
				cache.Add(metadata.MetadataType, sql, option);
			}

			return sql;
		}

		/// <summary>
		/// Gets the sql appropriate to the CrudOption, metadata and filters provided
		/// </summary>
		/// <param name="option"></param>
		/// <param name="metadata"></param>
		/// <param name="filters"></param>
		/// <returns></returns>
		protected string GetSql(CrudOption option, TableMetadata metadata, FilterByCollection filters)
		{
			// filters will generally not be the same so we need to 
			// generate every time...
			return GenerateSql(option, metadata, filters); 
		}

		private static string GenerateSql(CrudOption option, TableMetadata metadata, FilterByCollection filters)
		{
			var generator = new SqlStatementGenerator(metadata);

			return generator.GenerateFor(option, filters);
		}

		/// <summary>
		/// Runs arbitrary sql script against repository given an existing connection and transaction objects.
		/// </summary>
		/// <param name="sqlText"></param>
		/// <param name="connection"></param>
		/// <param name="transaction"></param>
		protected static void RunScript(string sqlText, DbConnection connection, DbTransaction transaction)
		{
			if (string.IsNullOrEmpty(sqlText))
			{
				throw new ArgumentNullException("sqlText");
			}

			if (connection == null)
			{
				throw new ArgumentNullException("connection");
			}

			if (transaction == null)
			{
				throw new ArgumentNullException("transaction");
			}

			using (DbCommand command = connection.CreateCommand(sqlText, transaction))
			{
				command.ExecuteNonQuery();
			}
		}

		private static T ReadObject<T>(DbCommand command, TableMetadata metadata) where T : new()
		{
			using (DbDataReader reader = command.ExecuteReader())
			{
				ResultReader<T> resultReader = new ResultReader<T>(reader, metadata);

				return resultReader.ReadFirst();
			}
		}

		private ICollection<T> ReadList<T>(string sql, TableMetadata metadata) where T : new()
		{
			using (DbConnection connection = this.ConnectionProvider.Connection)
			{
				using (DbCommand command = connection.CreateCommand(sql, null))
				{
					return ReadList<T>(command, metadata);
				}
			}
		}

		private static ICollection<T> ReadList<T>(DbCommand command, TableMetadata metadata) where T : new()
		{
			using (DbDataReader reader = command.ExecuteReader())
			{
				ResultReader<T> resultReader = new ResultReader<T>(reader, metadata);
				
				return resultReader.Read();
			}
		}
	}
}
